package alvarodelrosal.chat.server.infraestructura;

import alvarodelrosal.chat.server.modelo.ChatUser;
import java.io.BufferedReader;
import java.io.File;
import java.io.FileReader;
import java.io.IOException;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

public class ChatUsersDBPersistence implements ChatUsersPersistence {

    private Connection dbConnection = null;
    private static ChatUsersPersistence persistence = null;
    private static Object lock = new Object();

    private ChatUsersDBPersistence() {
        BufferedReader reader = null;
        try {
            String file = "config.txt";
            reader = new BufferedReader(new FileReader(new File(file)));
            String url = reader.readLine();
            String username = reader.readLine();
            String password = reader.readLine();
            try {
                Class.forName("org.apache.derby.jdbc.ClientDriver");
                dbConnection = DriverManager.getConnection(url, username, password);
            } catch (ClassNotFoundException ex) {
                Logger.getLogger(ChatUsersDBPersistence.class.getName()).log(Level.SEVERE, null, ex);
            } catch (SQLException ex) {
                Logger.getLogger(ChatUsersDBPersistence.class.getName()).log(Level.SEVERE, null, ex);
            }
        } catch (IOException ex) {
            Logger.getLogger(ChatUsersDBPersistence.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            if (reader != null) {
                try {
                    reader.close();
                } catch (IOException ex) {
                    Logger.getLogger(ChatUsersDBPersistence.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }
    }

    public static ChatUsersPersistence getPersistence() {
        if (persistence == null) {
            persistence = new ChatUsersDBPersistence();
        }
        return persistence;
    }

    @Override
    public boolean existsTheUser(String username) {
        PreparedStatement statement = null;
        ResultSet result = null;
        try {
            synchronized (lock) {
                statement = dbConnection.prepareStatement(""
                        + "SELECT COUNT(*) AS numero"
                        + "  FROM users"
                        + " WHERE User_Username = ?");
            }
            statement.setString(1, username);
            result = statement.executeQuery();
            result.next();
            return result.getInt("numero") > 0;
        } catch (SQLException ex) {
            Logger.getLogger(ChatUsersDBPersistence.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException ex) {
                    Logger.getLogger(ChatUsersDBPersistence.class.getName()).log(Level.SEVERE, null, ex);
                } finally {
                    if (result != null) {
                        try {
                            result.close();
                        } catch (SQLException ex) {
                            Logger.getLogger(ChatUsersDBPersistence.class.getName()).log(Level.SEVERE, null, ex);
                        }
                    }
                }
            }
        }
        return false;
    }

    @Override
    public boolean existsTheUser(String username, String password) {
        PreparedStatement statement = null;
        ResultSet result = null;
        try {
            synchronized (lock) {
                statement = dbConnection.prepareStatement(""
                        + "SELECT COUNT(User_Id) AS numero"
                        + "  FROM users"
                        + " WHERE User_Username = ?"
                        + "   AND User_Password = ?");
            }
            statement.setString(1, username);
            statement.setString(2, password);
            result = statement.executeQuery();
            result.next();
            return result.getInt("numero") > 0;
        } catch (SQLException ex) {
            Logger.getLogger(ChatUsersDBPersistence.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException ex) {
                    Logger.getLogger(ChatUsersDBPersistence.class.getName()).log(Level.SEVERE, null, ex);
                } finally {
                    if (result != null) {
                        try {
                            result.close();
                        } catch (SQLException ex) {
                            Logger.getLogger(ChatUsersDBPersistence.class.getName()).log(Level.SEVERE, null, ex);
                        }
                    }
                }
            }
        }
        return false;
    }

    @Override
    public ChatUser getTheUser(String username, String password) {
        PreparedStatement statement = null;
        ResultSet result = null;
        try {
            synchronized (lock) {
                statement = dbConnection.prepareStatement(""
                        + "SELECT *"
                        + "  FROM users"
                        + " WHERE User_username = ?"
                        + "   AND User_password = ?");
            }
            statement.setString(1, username);
            statement.setString(2, password);
            result = statement.executeQuery();
            result.next();

            return new ChatUser(result.getInt("User_id"),
                    result.getString("User_username"),
                    result.getString("User_password"),
                    result.getString("User_name"),
                    result.getString("User_mail"),
                    result.getBoolean("User_isAdmin"));
        } catch (SQLException ex) {
            Logger.getLogger(ChatUsersDBPersistence.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException ex) {
                    Logger.getLogger(ChatUsersDBPersistence.class.getName()).log(Level.SEVERE, null, ex);
                } finally {
                    if (result != null) {
                        try {
                            result.close();
                        } catch (SQLException ex) {
                            Logger.getLogger(ChatUsersDBPersistence.class.getName()).log(Level.SEVERE, null, ex);
                        }
                    }
                }
            }
        }
        return null;
    }

    @Override
    public ChatUser getTheUser(int userId) {
        PreparedStatement statement = null;
        ResultSet result = null;
        try {
            synchronized (lock) {
                statement = dbConnection.prepareStatement(""
                        + "SELECT *"
                        + "  FROM users"
                        + " WHERE User_Id = ?");
            }
            statement.setInt(1, userId);
            result = statement.executeQuery();
            result.next();

            return new ChatUser(result.getInt("User_id"),
                    result.getString("User_username"),
                    result.getString("User_password"),
                    result.getString("User_name"),
                    result.getString("User_mail"),
                    result.getBoolean("User_isAdmin"));
        } catch (SQLException ex) {
            Logger.getLogger(ChatUsersDBPersistence.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException ex) {
                    Logger.getLogger(ChatUsersDBPersistence.class.getName()).log(Level.SEVERE, null, ex);
                } finally {
                    if (result != null) {
                        try {
                            result.close();
                        } catch (SQLException ex) {
                            Logger.getLogger(ChatUsersDBPersistence.class.getName()).log(Level.SEVERE, null, ex);
                        }
                    }
                }
            }
        }
        return null;
    }

    @Override
    public void addUser(ChatUser user) {
        PreparedStatement statement = null;
        ResultSet result = null;
        try {
            synchronized (lock) {
                statement = dbConnection.prepareStatement("SELECT USER_ID "
                        + "FROM users "
                        + "ORDER BY USER_ID DESC");
            }
            result = statement.executeQuery();
            result.next();

            int userId = result.getInt("User_id");
            userId++;

            synchronized (lock) {
                statement = dbConnection.prepareStatement("INSERT INTO USERS "
                        + "(USER_ID, USER_NAME, USER_USERNAME, USER_PASSWORD, USER_MAIL, USER_ISADMIN) "
                        + "VALUES (?, ?, ?, ?, ?, ?)");
            }
            statement.setInt(1, userId);
            statement.setString(2, user.getName());
            statement.setString(3, user.getUsername());
            statement.setString(4, user.getPassword());
            statement.setString(5, user.getMail());
            if (user.isAdmin()) {
                statement.setInt(6, 1);
            } else {
                statement.setInt(6, 0);
            }

            statement.execute();

        } catch (SQLException ex) {
            Logger.getLogger(ChatUsersDBPersistence.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException ex) {
                    Logger.getLogger(ChatUsersDBPersistence.class.getName()).log(Level.SEVERE, null, ex);
                } finally {
                    if (result != null) {
                        try {
                            result.close();
                        } catch (SQLException ex) {
                            Logger.getLogger(ChatUsersDBPersistence.class.getName()).log(Level.SEVERE, null, ex);
                        }
                    }
                }
            }
        }
    }

    @Override
    public void deleteUser(int userId) {
        PreparedStatement statement = null;
        try {
            synchronized (lock) {
                statement = dbConnection.prepareStatement("DELETE FROM users "
                        + "WHERE USER_ID = ?");
            }
            statement.setInt(1, userId);
            statement.execute();

        } catch (SQLException ex) {
            Logger.getLogger(ChatUsersDBPersistence.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException ex) {
                    Logger.getLogger(ChatUsersDBPersistence.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }
    }

    @Override
    public List<ChatUser> getAllUsers() {
        PreparedStatement statement = null;
        ResultSet result = null;
        List<ChatUser> users = new ArrayList();
        try {
            synchronized (lock) {
                statement = dbConnection.prepareStatement(""
                        + "SELECT *"
                        + "  FROM users");
            }
            result = statement.executeQuery();

            while (result.next()) {
                users.add(new ChatUser(result.getInt("User_id"),
                        result.getString("User_username"),
                        result.getString("User_password"),
                        result.getString("User_name"),
                        result.getString("User_mail"),
                        result.getBoolean("User_isAdmin")));
            }

            return users;
        } catch (SQLException ex) {
            Logger.getLogger(ChatUsersDBPersistence.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException ex) {
                    Logger.getLogger(ChatUsersDBPersistence.class.getName()).log(Level.SEVERE, null, ex);
                } finally {
                    if (result != null) {
                        try {
                            result.close();
                        } catch (SQLException ex) {
                            Logger.getLogger(ChatUsersDBPersistence.class.getName()).log(Level.SEVERE, null, ex);
                        }
                    }
                }
            }
        }
        return null;
    }
}
